<?php
class ModelCommunitySearch extends Model{
   public function getForums(){
       $query=$this->sql_cache->get('search.forums' . (int)$this->config->get('config_language_id'));

       if(!$query){
           $query=$this->db->query("SELECT f.forum_id, fn.forum_name FROM " . DB_PREFIX . "community_forum AS f LEFT JOIN " . DB_PREFIX . "community_forum_name_des_rule AS fn ON (fn.forum_id=f.forum_id AND fn.language_id='" . (int)$this->config->get('config_language_id') . "') WHERE f.forum_cat_id>'0' AND f.parent_id='0' AND f.forum_enabled='1' ORDER BY f.sort_order_id ASC, fn.forum_name ASC");

           $this->sql_cache->set('search.forums.' . (int)$this->config->get('config_language_id'),$query);
       }

       return $query->rows;
   }

    public function getSubForums($parent_forum_id){
        $encrypt_data=md5($parent_forum_id);

        $query=$this->sql_cache->get('search.subforums.' . $encrypt_data . '.' . (int)$this->config->get('config_language_id'));

        if(!$query){
            $query=$this->db->query("SELECT f.forum_id, fn.forum_name FROM " . DB_PREFIX . "community_forum AS f LEFT JOIN " . DB_PREFIX . "community_forum_name_des_rule AS fn ON (fn.forum_id=f.forum_id AND fn.language_id='" . (int)$this->config->get('config_language_id') . "') WHERE f.forum_cat_id>'0' AND f.parent_id='" . (int)$parent_forum_id . "' AND f.forum_enabled='1' ORDER BY f.sort_order_id ASC, fn.forum_name ASC");

            if($query->rows){
                $this->sql_cache->set('search.subforums.' . $encrypt_data . '.' . (int)$this->config->get('config_language_id'),$query);
            }
        }

        return $query->rows;
    }

    public function getSearchResultTotal($getData){
        $keyword=trim(str_replace('&quot;','"',$getData['kw']));

        if(!isset($getData['sw']) || empty($getData['sw'])){
            $getData['sw']='psm';
        }

        switch($getData['sw']){
            case 'ps':
            case 'ts':
                $cols='p.post_subject';
                break;
            case 'pm':
            case 'tm':
                $cols='p.post_text';
                break;
            default:
                $cols='p.post_subject, p.post_text';
                break;
        }

        $sql="SELECT COUNT(*) AS total FROM " . DB_PREFIX . "community_posts AS p LEFT JOIN " . DB_PREFIX . "community_topics AS t ON (t.topic_id=p.topic_id) WHERE MATCH(" . $cols . ") AGAINST ('" . $this->db->escape($keyword) . "' IN BOOLEAN MODE) AND p.post_deleted='0' AND p.post_approved='1'";

        if(isset($getData['au'])){
            if(strpos($getData['au'],'*')!==FALSE){
                $user_name=str_replace('*', '%', addcslashes($getData['au'], '[%_]'));

                $user_id_query=$this->db->query("SELECT user_id FROM " . DB_PREFIX . "community_users WHERE username like '" . $this->db->escape($user_name) . "'");
            }else{
                $user_id_query=$this->db->query("SELECT user_id FROM " . DB_PREFIX . "community_users WHERE username ='" . $this->db->escape($getData['au']) . "'");
            }

            $user_id_str='';

            foreach($user_id_query->rows As $user_id){
                $user_id_str.=$user_id['user_id'] . ',';
            }

            $user_id_str=rtrim($user_id_str,',');

            if(!empty($user_id_str)){
                $sql.=(" AND p.poster_id IN (" . $user_id_str . ")");
            }else{
                return 0;
            }
        }

        if($getData['sw']=='ts' || $getData['sw']=='tm' || $getData['sw']=='tsm'){
            $sql.=" AND p.post_id=t.topic_first_post_id";
        }

        if(isset($getData['if'])){
            $sql.=" AND p.forum_id='" . (int)$getData['if'] . "'";
        }

        if(isset($getData['lr'])){
            switch($getData['lr']){
                case '1d':
                    $sql.=" AND " . time() . "-p.post_time<=" . (60*60*24);
                    break;
                case '1w':
                    $sql.=" AND " . time() . "-p.post_time<=" . (60*60*24*7);
                    break;
                case '2w':
                    $sql.=" AND " . time() . "-p.post_time<=" . (60*60*24*14);
                    break;
                case '1m':
                    $sql.=" AND " . time() . "-p.post_time<=" . (60*60*24*30);
                    break;
                case '3m':
                    $sql.=" AND " . time() . "-p.post_time<=" . (60*60*24*90);
                    break;
                case '6m':
                    $sql.=" AND " . time() . "-p.post_time<=" . (60*60*24*180);
                    break;
                case '1y':
                    $sql.=" AND " . time() . "-p.post_time<=" . (60*60*24*365);
                    break;
            }
        }

        $encrypt_data=md5($sql);
        $query=$this->sql_cache->get('search.searchresulttotal.' . $encrypt_data);

        if(!$query){
            $query=$this->db->query($sql);

            $this->sql_cache->set('search.searchresulttotal.' . $encrypt_data,$query,TRUE,(60*60*2));
        }

        return $query->row['total'];
   }

   public function getSearchResult($getData,$start,$limit){
       $keyword=trim(str_replace('&quot;','"',$getData['kw']));

       if(!isset($getData['sw']) || empty($getData['sw'])){
           $getData['sw']='psm';
       }

       switch($getData['sw']){
           case 'ps':
           case 'ts':
               $cols='p.post_subject';
               break;
           case 'pm':
           case 'tm':
               $cols='p.post_text';
               break;
           default:
               $cols='p.post_subject, p.post_text';
       }

       $sql="SELECT p.post_id, p.post_subject, p.post_text, p.post_time, u.user_id, u.username, f.forum_id, fn.forum_name, t.topic_id, t.topic_title, t.topic_views, t.topic_replies FROM " . DB_PREFIX . "community_posts AS p LEFT JOIN " . DB_PREFIX . "community_topics AS t ON (t.topic_id=p.topic_id) LEFT JOIN " . DB_PREFIX . "community_users AS u on (u.user_id=p.poster_id) LEFT JOIN " . DB_PREFIX . "community_forum AS f ON (f.forum_id=p.forum_id) LEFT JOIN " . DB_PREFIX . "community_forum_name_des_rule AS fn ON (fn.forum_id=p.forum_id AND fn.language_id='" . (int)$this->config->get('config_language_id') . "') WHERE MATCH(" . $cols . ") AGAINST ('" . $this->db->escape($keyword) . "' IN BOOLEAN MODE) AND p.post_deleted='0' AND p.post_approved='1'";

       if(isset($getData['au'])){
           if(strpos($getData['au'],'*')!==FALSE){
               $user_name=str_replace('*', '%', addcslashes($getData['au'], '[%_]'));

               $user_id_query=$this->db->query("SELECT user_id FROM " . DB_PREFIX . "community_users WHERE username like '" . $this->db->escape($user_name) . "'");
           }else{
               $user_id_query=$this->db->query("SELECT user_id FROM " . DB_PREFIX . "community_users WHERE username ='" . $this->db->escape($getData['au']) . "'");
           }

           $user_id_str='';

           foreach($user_id_query->rows As $user_id){
               $user_id_str.=$user_id['user_id'] . ',';
           }

           $user_id_str=rtrim($user_id_str,',');

           if(!empty($user_id_str)){
               $sql.=(" AND p.poster_id IN (" . $user_id_str . ")");
           }else{
               return array();
           }
       }

       if($getData['sw']=='ts' || $getData['sw']=='tm' || $getData['sw']=='tsm'){
           $sql.=" AND p.post_id=t.topic_first_post_id";
       }

       if(isset($getData['if'])){
           $sql.=" AND p.forum_id='" . (int)$getData['if'] . "'";
       }

       if(isset($getData['lr'])){
           switch($getData['lr']){
               case '1d':
                   $sql.=" AND " . time() . "-p.post_time<=" . (60*60*24);
                   break;
               case '1w':
                   $sql.=" AND " . time() . "-p.post_time<=" . (60*60*24*7);
                   break;
               case '2w':
                   $sql.=" AND " . time() . "-p.post_time<=" . (60*60*24*14);
                   break;
               case '1m':
                   $sql.=" AND " . time() . "-p.post_time<=" . (60*60*24*30);
                   break;
               case '3m':
                   $sql.=" AND " . time() . "-p.post_time<=" . (60*60*24*90);
                   break;
               case '6m':
                   $sql.=" AND " . time() . "-p.post_time<=" . (60*60*24*180);
                   break;
               case '1y':
                   $sql.=" AND " . time() . "-p.post_time<=" . (60*60*24*365);
                   break;
           }
       }

       if(isset($getData['sb'])){
           switch($getData['sb']){
               case 'lpt':
                   $sql.=(isset($getData['or']) && $getData['or']=='desc' ? " ORDER BY t.topic_last_post_time DESC, p.post_time DESC" : " ORDER BY t.topic_last_post_time ASC, p.post_time ASC");
                   break;
               case 'tt':
                   $sql.=(isset($getData['or']) && $getData['or']=='desc' ? " ORDER BY t.topic_time DESC, p.post_time DESC" : " ORDER BY t.topic_time ASC, p.post_time ASC");
                   break;
               case 'tv':
                   $sql.=(isset($getData['or']) && $getData['or']=='desc' ? " ORDER BY t.topic_views DESC, p.post_time DESC" : " ORDER BY t.topic_views ASC, p.post_time ASC");
                   break;
               case 'tr':
                   $sql.=(isset($getData['or']) && $getData['or']=='desc' ? " ORDER BY t.topic_replies DESC, p.post_time DESC" : " ORDER BY t.topic_replies ASC, p.post_time ASC");
                   break;
               case 'f':
                   $sql.=(isset($getData['or']) && $getData['or']=='desc' ? " ORDER BY p.forum_id DESC" : " ORDER BY p.forum_id ASC");
                   break;
               case 'a':
                   $sql.=(isset($getData['or']) && $getData['or']=='desc' ? " ORDER BY u.username DESC" : "ORDER BY u.username ASC");
                   break;
           }
       }

       $sql.=" LIMIT " . $start . ", " .  $limit;

       $encrypt_data=md5($sql);

       $query=$this->sql_cache->get('search.searchresult.' . $encrypt_data);

       if(!$query){
           $query=$this->db->query($sql);

           $this->sql_cache->set('search.searchresult.' . $encrypt_data,$query,TRUE,(60*60*2));
       }

       return $query->rows;
   }

    public function getUnansweredTopicTotal(){
        $query=$this->sql_cache->get('search.unansweredtopictotal');

        if(!$query){
            $query=$this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "community_topics WHERE topic_replies='0' AND topic_approved='1' AND topic_deleted='0'");

            $this->sql_cache->set('search.unansweredtopictotal',$query,TRUE,(60*30));
        }

        return $query->row['total'];
    }

    public function getUnansweredTopics($start, $limit){
        $sql="SELECT p.post_id, p.post_subject, p.post_text, p.post_time, u.user_id, u.username, f.forum_id, fn.forum_name, t.topic_id, t.topic_title, t.topic_views, t.topic_replies FROM " . DB_PREFIX . "community_topics AS t LEFT JOIN " . DB_PREFIX . "community_posts AS p ON (p.post_id=t.topic_first_post_id) LEFT JOIN " . DB_PREFIX . "community_users AS u on (u.user_id=p.poster_id) LEFT JOIN " . DB_PREFIX . "community_forum AS f ON (f.forum_id=t.forum_id) LEFT JOIN " . DB_PREFIX . "community_forum_name_des_rule AS fn ON (fn.forum_id=t.forum_id AND fn.language_id='" . (int)$this->config->get('config_language_id') . "') WHERE t.topic_replies='0' AND t.topic_approved='1' AND t.topic_deleted='0' ORDER BY t.topic_last_post_time DESC LIMIT " . $start . ", " .  $limit;

        $encrypt_data=md5($sql);

        $query=$this->sql_cache->get('search.unansweredtopics.' . $encrypt_data);

        if(!$query){
            $query=$this->db->query($sql);

            $this->sql_cache->set('search.unansweredtopics.' . $encrypt_data,$query,TRUE,(60*30));
        }

        return $query->rows;
    }
}
?>